package com.ustcinfo.study.hive.r5.wangjie;

import java.sql.*;

/**
 * @ author WJ
 * @ data 2018/08/03 20:00
 */
public class WjHiveConnectionTest{

    // hive链接地址
    private static String url = "jdbc:hive2://WJ1:10000/default";

    //hive用户名
    private static String userName = "root";

    // hive密码
    private static String passwd = "";

    // hive 驱动
    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;//定义静态数据库连接变量
    private static Statement statement = null;//定义静态Statement 变量
    private static ResultSet resultSet = null;//定义静态结果集


    // 创建内部表test sql   ("ip", "domain_name", "access_time", "target_ip", "status")
   private static String createSql = "create table wj(ip string ,domain_name string ,access_time string ,target_ip string ,status string)row format delimited fields terminated by '#' ";

    // 加载数据到wj sql
    private static String localDataSql = "load data local inpath '/root/dnsSample' into table wj ";

    //统计出test表中各域名出现的次数
    private static String  countSql = "select domain_name,count(*) counts from wj group by domain_name order by counts desc ";




    public static void main(String[] args) throws SQLException {
        try{
            //  加载hive驱动
              Class.forName(driver);
            // 连接hive
            connection = DriverManager.getConnection(url, userName, passwd);
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
            }

            //创建Statement对象
            statement = connection.createStatement();

            //创建表（wj)
           statement.execute(createSql);

           //从本地加载数据
            statement.execute(localDataSql);

            // 查询域名出现的次数
            resultSet = statement.executeQuery(countSql);

            //遍历结果集
            while (resultSet.next()) {
                System.out.println("域名：" + resultSet.getString("domain_name") + "出现次数是：" + resultSet.getInt("counts"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }finally {
            //关闭结果集
            if (resultSet != null) {
                resultSet.close();
            }
            //关闭Statemete 对象
            if (statement != null) {
                statement.close();
            }
            //关闭连接
            if(connection != null) {
                connection.close();
            }
        }
    }
}
